package tech.jdbc.dao.impl;

import tech.jdbc.dao.ITeacherDao;
import tech.jdbc.entity.Teacher;
import tech.jdbc.util.JdbcUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Create with Intellij IDEA
 *
 * @Author Q
 * @date 2021/11/25/15:53
 */
public class TeacherDaoImpl implements ITeacherDao {
    @Override
    public List<Teacher> getById(Integer id) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Teacher> teacherList = new ArrayList<>();
        try {
            conn = JdbcUtil.getConnection();

            String sql = "select teacher.id,teacher.name,course.id cid,course.cname from teacher\n" +
                    "left join course on teacher.id=course.teacher_id WHERE teacher.id=?";
            pst = conn.prepareStatement(sql);

            pst.setInt(1,id);

            rs = pst.executeQuery();
            while (rs.next()){
                Integer tId = rs.getInt(1);
                String name = rs.getString(2);
                Integer CId = rs.getInt(3);
                String CName = rs.getString(4);

                Teacher teacher = new Teacher(tId,name,CId,CName);

                teacherList.add(teacher);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(conn,pst);
        }
        return teacherList;
    }

    @Override
    public int delById(Integer id) {
        Connection conn = null;
        PreparedStatement pst = null;

        int count = 0;

        try {
            conn = JdbcUtil.getConnection();

            String sql = "delete teacher,course from teacher left join course \n" +
                    "on teacher.id=course.teacher_id where teacher.id=?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1,id);

            count = pst.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(conn,pst);
        }
        return count;
    }
}
